import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.dialogs.dialogs import Messagebox

from datetime import datetime

from model import db, Clazz, Student

# 学生信息全量查询界面
class StudentFrame(ttk.Frame):

    def __init__(self, master: ttk.Window):
        super().__init__(master, padding=10)
        self.pack(fill=BOTH, expand=YES)

        # 学生全量查询区域
        self.student_frame = ttk.Frame(master=self)
        self.student_frame.pack(fill=X, pady=1, side=TOP)
        self._create_student_frame()

        # 初始化分页相关参数和填充班级下拉列表框
        self._init_page_para()
        # 查询学生列表
        self._student_list()

    # 学生全量查询区域
    def _create_student_frame(self):
        # 查询条件区域
        self._create_search_area()
        # 学生列表区域
        self._create_student_list_area()
        # 分页区域
        self._create_page_area()

    # 查询条件区域
    def _create_search_area(self):
        row_1 = ttk.Frame(self.student_frame)
        row_1.pack(fill=X, expand=YES)

        sutno_lbl = ttk.Label(row_1, text="学号")
        sutno_lbl.pack(side=LEFT, padx=10)
        self.stuno_ent = ttk.Entry(row_1)
        self.stuno_ent.pack(side=LEFT)

        sutname_lbl = ttk.Label(row_1, text="姓名")
        sutname_lbl.pack(side=LEFT, padx=10)
        self.stuname_ent = ttk.Entry(row_1)
        self.stuname_ent.pack(side=LEFT)

        gender_lbl = ttk.Label(row_1, text="性别")
        gender_lbl.pack(side=LEFT, padx=10)
        self.gender_combo = ttk.Combobox(row_1, state=READONLY)
        self.gender_combo["values"] = ("请选择", "男", "女")
        self.gender_combo.current(0)
        self.gender_combo.pack(side=LEFT)

        class_lbl = ttk.Label(row_1, text="班级")
        class_lbl.pack(side=LEFT, padx=10)
        self.class_combo = ttk.Combobox(row_1, state=READONLY)
        self.class_combo.pack(side=LEFT)

        row_2 = ttk.Frame(self.student_frame)
        row_2.pack(fill=X, expand=YES)

        birth_lbl = ttk.Label(row_2, text="出生日期")
        birth_lbl.pack(side=LEFT, padx=10, pady=10)
        self.birth_start_ent = ttk.DateEntry(row_2, dateformat=r"%Y-%m-%d", startdate=datetime.strptime("2000-01-01", "%Y-%m-%d"))
        self.birth_start_ent.pack(side=LEFT)
        _lbl = ttk.Label(row_2, text="---")
        _lbl.pack(side=LEFT, padx=10, pady=10)
        self.birth_end_ent = ttk.DateEntry(row_2, dateformat=r"%Y-%m-%d", startdate=datetime.strptime("2099-12-31", "%Y-%m-%d"))
        self.birth_end_ent.pack(side=LEFT)

        phone_lbl = ttk.Label(row_2, text="联系方式")
        phone_lbl.pack(side=LEFT, padx=10)
        self.phone_ent = ttk.Entry(row_2)
        self.phone_ent.pack(side=LEFT)

        search_btn = ttk.Button(
            master=row_2,
            text="搜索",
            style=DANGER,
            width=10,
            command=self._search
        )
        search_btn.pack(side=LEFT, padx=15)

    # 学生列表区域
    def _create_student_list_area(self):
        self.stuview = ttk.Treeview(
            master=self,
            bootstyle=INFO,
            columns=[0, 1, 2, 3 , 4, 5, 6],
            show=HEADINGS
        )
        self.stuview.pack(fill=BOTH, expand=YES, pady=10)
        self.stuview.heading(0, text="ID")
        self.stuview.heading(1, text="学号")
        self.stuview.heading(2, text="姓名")
        self.stuview.heading(3, text="班级")
        self.stuview.heading(4, text="出生日期")
        self.stuview.heading(5, text="性别")
        self.stuview.heading(6, text="联系方式")
        self.stuview.column(
            column=0,
            width=20
        )
        self.stuview.column(
            column=1,
            width=80
        )
        self.stuview.column(
            column=2,
            width=80
        )
        self.stuview.column(
            column=3,
            width=80
        )
        self.stuview.column(
            column=4,
            width=100
        )
        self.stuview.column(
            column=5,
            width=60
        )

    # 分页区域
    def _create_page_area(self):
        pagebar = ttk.Frame(self)
        pagebar.pack(fill=X, pady=1, side=BOTTOM)

        page_row1 = ttk.Frame(pagebar)
        page_row1.pack(fill=X, expand=YES)

        btn = ttk.Button(
            master=page_row1,
            text="跳转",
            style=DANGER,
            command=self._go
        )
        btn.pack(side=RIGHT, padx=10)

        self.pageno_ent = ttk.Entry(page_row1, width=5)
        self.pageno_ent.pack(side=RIGHT)
        self.pageno_ent.insert(0, "1")

        page_row2 = ttk.Frame(pagebar)
        page_row2.pack(fill=X, expand=YES, pady=5)

        btn = ttk.Button(
            master=page_row2,
            text="尾页",
            command=self._end_page
        )
        btn.pack(side=RIGHT, padx=10)

        btn = ttk.Button(
            master=page_row2,
            text="下一页",
            command=self._next
        )
        btn.pack(side=RIGHT)

        self.page_lbl = ttk.Label(page_row2, text="1/1")
        self.page_lbl.pack(side=RIGHT, padx=5)

        btn = ttk.Button(
            master=page_row2,
            text="上一页",
            command=self._prev
        )
        btn.pack(side=RIGHT, padx=5)

        btn = ttk.Button(
            master=page_row2,
            text="首页",
            command=self._first_page
        )
        btn.pack(side=RIGHT, padx=5)

    def _init_page_para(self):
        """ 初始化分页相关参数和填充班级列表 """
        self.page_no = 1    # 页码
        self.page_size = 20 # 每页显示条数

        # 获取班级列表并填充到班级下拉列表中
        clazz_list = ["请选择"]
        items = Clazz.select(Clazz.name).order_by(Clazz.id.desc())
        for item in items:
            clazz_list.append(item.name)
        self.class_combo["values"] = clazz_list
        self.class_combo.current(0)

    def _search(self):
        """ 查询方法，“查询”按钮点击时使用 """
        self.page_no = 1
        self.pageno_ent.delete(0, END)
        self.pageno_ent.insert(0, str(self.page_no))
        self._student_list()

    def _go(self):
        """ 跳转，“跳转”按钮点击时使用 """
        if self.pageno_ent.get():
            pageno = int(self.pageno_ent.get())
            if 0 >= pageno:
                Messagebox.show_error("请输入大于0的页码", title="提示")
                return
            if pageno > self.pages:
                Messagebox.show_error("请输入不大于总页数的页码", title="提示")
                return
            self.page_no = pageno
            self._student_list()
        else:
            Messagebox.show_error("请输入跳转页码", title="提示")

    def _prev(self):
        """ 上一页，“上一页”按钮点击时使用 """
        if self.page_no - 1 > 0:
            self.page_no -= 1
            self._student_list()

    def _next(self):
        """ 下一页，“下一页”按钮点击时使用 """
        if self.page_no + 1 <= self.pages:
            self.page_no += 1
            self._student_list()

    def _first_page(self):
        """ 首页，“首页”按钮点击时使用 """
        self.page_no = 1
        self._student_list()

    def _end_page(self):
        """ 尾页，“尾页”按钮点击时使用 """
        self.page_no = self.pages
        self._student_list()

    def _student_list(self) -> list:
        """ 根据条件查询对应的学生列表信息
                返回值：对应的学生列表信息
        """
        items = self.stuview.get_children()
        for item in items:
            self.stuview.delete(item)

        # 参数
        params = [self.birth_start_ent.entry.get(), self.birth_end_ent.entry.get()]
        count_sql = "select count(*) from student where birth between ? and ?"
        sql = "select id, stuno, name, clazzname, birth, gender, phone from student where birth between ? and ?"
        tmp = ""
        if self.stuno_ent.get():
            tmp += " and stuno like ?"
            params.append("%" + self.stuno_ent.get() + "%")
        if self.stuname_ent.get():
            tmp += " and name like ?"
            params.append("%" + self.stuname_ent.get() + "%")
        if self.gender_combo.get() != "请选择":
            tmp += " and gender=?"
            params.append(self.gender_combo.get())
        if self.class_combo.get() != "请选择":
            tmp += " and clazzname like ?"
            params.append("%" + self.class_combo.get() + "%")
        if self.phone_ent.get():
            tmp += " and phone like ?"
            params.append("%" + self.phone_ent.get() + "%")
        tmp += " order by id desc limit ? offset ?"
        params.append(self.page_size)
        params.append((self.page_no - 1) * self.page_size)

        # 执行查询总数量 SQL 语句
        count_sql += tmp
        cursor = db.execute_sql(count_sql, tuple(params))
        for row in cursor.fetchall():
            self.total_count = row[0]                                               # 数据总量
            self.pages = (self.total_count + self.page_size - 1) // self.page_size  # 总页数

        # 执行查询 SQL 语句
        sql += tmp
        cursor = db.execute_sql(sql, tuple(params))
        for row in cursor.fetchall():
            self.stuview.insert(
                parent="",
                index=END,
                values=row
            )

        # 设置分页显示
        self.page_lbl.configure(text=f"{self.page_no}/{self.pages}")